<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ page language="java" import="java.sql.*"%>

<%!public String getQuarter(int i) {
		String quarter;
		switch (i) {
		case 1:
			quarter = "Fall";
			break;

		case 2:
			quarter = "Winter";
			break;

		case 3:
			quarter = "Spring";
			break;

		case 4:
			quarter = "Summer";
			break;
		default:
			quarter = "ERROR";
		}

		return quarter;
	}%>
<%
	Class.forName("org.postgresql.Driver");
	Connection conn = DriverManager.getConnection(
			"jdbc:postgresql://localhost:5432/cse132b", "cse132blogin",
			"1234567890");
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
	<form action="report2a.jsp" method="post">
	<table border="1">
		<tr>
			<td valign="top" width="30%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<%
			if ( request.getParameter("ssn") == null ) {
				
				PreparedStatement pstmt;
				pstmt = conn.prepareStatement("SELECT * FROM students");
			%>
			<td>Select a Student<br>
				<select id="ssn" name="ssn">
					<%
					ResultSet rsStudents = pstmt.executeQuery();
					while (rsStudents.next()) {
					%>	
						<option value="<%=rsStudents.getInt("ssn") %>"><%=(rsStudents.getString("first_name") + (rsStudents.getString("middle_name") != null ? (" " + rsStudents.getString("middle_name")) : "") + " " + rsStudents.getString("last_name")) %> - <%=rsStudents.getInt("ssn") %></option>
					<%
					}
					%>
				</select>
				<input type="submit" value="Get Report"/>
			</td>
			<%
			} else {
				Integer ssn = Integer.parseInt(request.getParameter("ssn"));
			%>
			<td>Report 2a<br>
				<b>Conflicting Classes</b><br/>
				<%
				PreparedStatement conflictingClasses = conn.prepareStatement("SELECT courses.department_abbreviation, courses.course_number, classes.title FROM sections LEFT JOIN classes ON (sections.class_id = classes.class_id) LEFT JOIN courses ON (classes.course_id = courses.course_id) WHERE sections.section_id IN (SELECT m1.section_id FROM meetings m1 WHERE m1.section_id NOT IN (SELECT enroll.section_id FROM enroll LEFT JOIN sections ON (enroll.section_id = sections.section_id) LEFT JOIN classes ON (sections.class_id = classes.class_id) LEFT JOIN students ON (enroll.student_id = students.student_id) WHERE quarter = '2' AND year = 2014 AND students.ssn = ?) AND EXISTS (SELECT * FROM meetings m2 WHERE m2.section_id IN (SELECT enroll.section_id FROM enroll LEFT JOIN sections ON (enroll.section_id = sections.section_id) LEFT JOIN classes ON (sections.class_id = classes.class_id) LEFT JOIN students ON (enroll.student_id = students.student_id) WHERE quarter = '2' AND year = 2014 AND students.ssn = ?) AND ((m1.meeting_start_time >= m2.meeting_start_time AND m1.meeting_start_time <= m2.meeting_end_time) OR (m1.meeting_end_time >= m2.meeting_start_time AND m1.meeting_end_time <= m2.meeting_end_time)) AND m1.meeting_day = m2.meeting_day)) GROUP BY courses.department_abbreviation, courses.course_number, classes.title, classes.class_id;");
				conflictingClasses.setString(1, ssn.toString());
				conflictingClasses.setString(2, ssn.toString());
				ResultSet rsConflictingClasses = conflictingClasses.executeQuery();
				while(rsConflictingClasses.next()) {
				%>
				<%=rsConflictingClasses.getString("department_abbreviation") %> <%=rsConflictingClasses.getString("course_number") %> <%=rsConflictingClasses.getString("title") %><br/>
				<% } %>
				<b>Classes you can still enroll in</b><br/>
				<%
				PreparedStatement availableClasses = conn.prepareStatement("SELECT sections.section_id, courses.department_abbreviation, courses.course_number, classes.title, sections.section_id FROM sections LEFT JOIN classes ON (sections.class_id = classes.class_id) LEFT JOIN courses ON (classes.course_id = courses.course_id) WHERE sections.section_id NOT IN (SELECT m1.section_id FROM meetings m1 WHERE EXISTS (SELECT * FROM meetings m2 WHERE m2.section_id IN (SELECT enroll.section_id FROM enroll LEFT JOIN sections ON (enroll.section_id = sections.section_id) LEFT JOIN classes ON (sections.class_id = classes.class_id) LEFT JOIN students ON (enroll.student_id = students.student_id) WHERE quarter = '2' AND year = 2014 AND students.ssn = ?) AND ((m1.meeting_start_time >= m2.meeting_start_time AND m1.meeting_start_time <= m2.meeting_end_time) OR (m1.meeting_end_time >= m2.meeting_start_time AND m1.meeting_end_time <= m2.meeting_end_time)) AND m1.meeting_day = m2.meeting_day))");
				availableClasses.setString(1, ssn.toString());
				ResultSet rsAvailableClasses = availableClasses.executeQuery();
				while (rsAvailableClasses.next()) {
				%>
				<%=rsAvailableClasses.getString("department_abbreviation") %> <%=rsAvailableClasses.getString("course_number") %> Section <%=rsAvailableClasses.getInt("section_id") %> <%=rsAvailableClasses.getString("title") %><br/>
				<%
				}
				%>
			</td>
			<% } %>
		</tr>
	</table>
	</form>
</body>
<%
	// Close the Connection
	conn.close();
%>